package com.example.app2.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.example.app2.pojo.User;

import java.util.ArrayList;
import java.util.List;

public class UserHelper extends SQLiteOpenHelper {
    static String DB_NAME="user.db";
    static int DB_VERSION=2;
    private static UserHelper mHelper=null;

    private SQLiteDatabase mRDB=null;
    private SQLiteDatabase mWDB=null;
    private static String TABLE_NAME="User";

    private UserHelper(@Nullable Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    public  static  UserHelper getInstance(Context context)
    {
        if(mHelper==null)
        {
            mHelper=new UserHelper(context);
        }
        return mHelper;
    }
    //打开读连接
    public SQLiteDatabase openReadLink()
    {
        if(mRDB==null||!mRDB.isOpen())
        {
            mRDB=mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    //打开写连接
    public SQLiteDatabase openWriteLink()
    {
        if(mWDB==null||!mWDB.isOpen())
        {
            mWDB=mHelper.getReadableDatabase();
        }
        return mWDB;
    }
    //关闭数据库连接
    public void closeLink()
    {
        if(mRDB!=null&&mRDB.isOpen())
        {
            mRDB.close();
            mRDB=null;
        }
        if(mWDB!=null&&mWDB.isOpen())
        {
            mWDB.close();
            mWDB=null;
        }
    }



    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
                " (" + "id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL," +
                "name VARCHAR NOT NULL," + "age INTEGER NOT NULL," +
                "height INTEGER NOT NULL," + "weight FLOAT NOT NULL," +
                "married INTEGER NOT NULL);" ;
        db.execSQL(create_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql1="ALTER TABLE "+TABLE_NAME+" ADD COLUMN phone VARCHAR;";
        db.execSQL(sql1);

        String sql2="ALTER TABLE "+TABLE_NAME+" ADD COLUMN password VARCHAR;";
        db.execSQL(sql2);
    }
    public long insert(User user)
    {
        ContentValues values=new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);
        //第二个参数是为了避免插入键和值都是空的段，就是假如第三个参数为空的情况，为null则表示不插入，一般情况写null即可。
        try{
            mWDB.beginTransaction();
            mWDB.insert(TABLE_NAME,null,values);
            int i=1/0;
            mWDB.insert(TABLE_NAME,null,values);
            mWDB.setTransactionSuccessful();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            mWDB.endTransaction();
        }
        return 1;
    }
    public long deleteByName(String name)
    {
        return mWDB.delete(TABLE_NAME,"name=?",new String[]{name});
    }
    public long upDate(User user)
    {
        ContentValues values=new ContentValues();
        values.put("name",user.name);
        values.put("age",user.age);
        values.put("height",user.height);
        values.put("weight",user.weight);
        values.put("married",user.married);
        return mWDB.update(TABLE_NAME,values,"name=?",new String[]{user.name});
    }
    public List<User> queryByName(String name)
    {
        Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null, null);
        List<User> list=new ArrayList<>();
        while(cursor.moveToNext())
        {
            User user =new User();
            user.name=cursor.getString(1);
            user.age=cursor.getInt(2);
            user.height=cursor.getFloat(3);
            user.weight=cursor.getFloat(4);
            user.married=cursor.getInt(5)==1?true:false;
            list.add(user);
        }
        return list;
    }

    public List<User> queryAll()
    {
        Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null, null);
        List<User> list=new ArrayList<>();
        while(cursor.moveToNext())
        {
            User user =new User();
            user.name=cursor.getString(1);
            user.age=cursor.getInt(2);
            user.height=cursor.getFloat(3);
            user.weight=cursor.getFloat(4);
            user.married=cursor.getInt(5)==1?true:false;
            list.add(user);
        }
        return list;
    }
}
